﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    public class FailureTrackingManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        #region FailureTracking
        public bool SaveObj(Tbl_FailureTracking cuObj)
        {
            string sql = "";
            sql = string.Format(@"INSERT INTO Tbl_FailureTracking
          (TrackingDate,PartNo,FailureId,TeamId,Status,PhaseNo,ToEmpIds,TrackingEndDate,FailureLevel,RECORDERUSERID,RecordTime,
FAILURENAME,REMARK,FAILURETYPE) 
          VALUES({0},'{1}',{2},{3},{4},'{5}','{6}',{7},{8},{9},{10},'{11}','{12}',{13})"
              , (cuObj.TrackingDate.HasValue && cuObj.TrackingDate.Value.Year != 1900 ?
              "to_date('" + cuObj.TrackingDate.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
             cuObj.PartNo, cuObj.FailureId, cuObj.TeamId, cuObj.Status ? 1 : 0,
             cuObj.PhaseNo, cuObj.ToEmpIds,
             (cuObj.TrackingEndDate.HasValue && cuObj.TrackingEndDate.Value.Year != 1900 ?
             "to_date('" + cuObj.TrackingEndDate.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
             cuObj.FailureLevel, cuObj.RecorderUserId,
             (cuObj.RecordTime.HasValue && cuObj.RecordTime.Value.Year != 1900 ?
              "to_date('" + cuObj.RecordTime.Value.ToString("yyyy-MM-dd") + "','yyyy-MM-dd')" : "NULL"),
              cuObj.FailureName, cuObj.Remark, cuObj.FAILURETYPE

              );
            return accessHelper.ExcultData(sql);
        }

        public List<Tbl_FailureTracking> GetObjListByCondition(int cuEmpId, string partName, string failureName,
          DateTime? sTrakingTime, DateTime? eTrakingTime,
          int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by tr.Id desc) recordId,tr.*,
              pt.PartName,pt.en_PartName,fi.FailureName jghFailureName,fi.en_FailureName,reEmp.empName RecordUserName
              from Tbl_FailureTracking tr 
              left join Tbl_FailureInfo fi on fi.Id=tr.FailureId 
              left join tbl_Part pt on pt.PartNo=tr.PartNo 
              left join Tbl_Employee reEmp on reEmp.id=tr.RECORDERUSERID
              where 1=1 and tr.Status=1");

            if (cuEmpId > 0)
                sql = sql.AppendFormat(@" and CONCAT(CONCAT(',',tr.ToEmpIds),',') like '%,{0},%' ", cuEmpId);
            if (!string.IsNullOrEmpty(partName))
                sql = sql.AppendFormat(@" and pt.en_PartName like '%{0}%'", partName);
            if (!string.IsNullOrEmpty(failureName))
                sql = sql.AppendFormat(@" and fi.en_FailureName like '%{0}%'", failureName);

            if (sTrakingTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - tr.TrackingDate))>=0", sTrakingTime.Value.ToString("yyyy-MM-dd"));
            if (eTrakingTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - tr.TrackingDate))<=0", eTrakingTime.Value.ToString("yyyy-MM-dd"));

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, @"select re.* from (");
            sql.AppendFormat(@") re 
            where re.recordId>{0} and re.recordId<={1}", startNum, endNum);

            sql.Insert(0, @"select re1.*          
           ,dict1.DictName PhaseName, dict1.en_DictName en_PhaseName
           ,te.TeamName,te.en_TeamName 
           ,RTRIM(replace(replace(replace(replace(replace(replace((
           select SYS_XMLAGG(SYS_XMLGEN(emp.EmpName)) from Tbl_Employee emp              
           where instr(CONCAT(CONCAT(',',re1.ToEmpIds),','),CONCAT(CONCAT(',',to_char(emp.id)),','))>0
           ) ,'</EMPNAME>',','),'<EMPNAME>',''),'<?xml version=""1.0""?>',''),'<ROWSET>',''),'</ROWSET>',''),chr(10),''),',') ToEmpNames 
            from (");
            sql.AppendFormat(@") re1           
          left join Tbl_DictInfo dict1 on dict1.PId=24 and dict1.DictValue=re1.PhaseNo 
          left join Tbl_Team te on te.Id=re1.TeamId ");
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_FailureTracking> returnlist = new List<Tbl_FailureTracking>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_FailureTracking
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    PartNo = dr["PartNo"] == DBNull.Value ? "" : dr["PartNo"].ToString(),
                    FailureId = dr["FailureId"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureId"].ToString()),
                    //PositionNo = dr["PositionNo"] == DBNull.Value ? "" : dr["PositionNo"].ToString(),
                    PhaseNo = dr["PhaseNo"] == DBNull.Value ? "" : dr["PhaseNo"].ToString(),
                    TrackingDate = dr["TrackingDate"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["TrackingDate"].ToString()),
                    Status = dr["Status"] == DBNull.Value ? false : dr["Status"].ToString() == "1" ? true : false,
                    TeamId = dr["TeamId"] == DBNull.Value ? 0 : Int32.Parse(dr["TeamId"].ToString()),
                    ToEmpIds = dr["ToEmpIds"] == DBNull.Value ? "" : dr["ToEmpIds"].ToString(),
                    ToEmpNames = dr["ToEmpNames"] == DBNull.Value ? "" : dr["ToEmpNames"].ToString(),
                    en_FailureName = Int32.Parse(dr["FAILURETYPE"].ToString()) == 0 ? (dr["en_FailureName"] == DBNull.Value ? "" : dr["en_FailureName"].ToString()) : (dr["jghFailureName"] == DBNull.Value ? "" : dr["jghFailureName"].ToString()),
                    en_PhaseName = dr["en_PhaseName"] == DBNull.Value ? "" : dr["en_PhaseName"].ToString(),
                    en_TeamName = dr["en_TeamName"] == DBNull.Value ? "" : dr["en_TeamName"].ToString(),
                    FailureName = Int32.Parse(dr["FAILURETYPE"].ToString()) == 0 ? (dr["FailureName"] == DBNull.Value ? "" : dr["FailureName"].ToString()) : (dr["jghFailureName"] == DBNull.Value ? "" : dr["jghFailureName"].ToString()),
                    //PositionName = dr["PositionName"] == DBNull.Value ? "" : dr["PositionName"].ToString(),
                    PhaseName = dr["PhaseName"] == DBNull.Value ? "" : dr["PhaseName"].ToString(),
                    TeamName = dr["TeamName"] == DBNull.Value ? "" : dr["TeamName"].ToString(),
                    en_PartName = dr["en_PartName"] == DBNull.Value ? "" : dr["en_PartName"].ToString(),
                    PartName = dr["PartName"] == DBNull.Value ? "" : dr["PartName"].ToString(),
                    FailureLevel = dr["FailureLevel"] == DBNull.Value ? 0 : Int32.Parse(dr["FailureLevel"].ToString()),
                    TrackingEndDate = dr["TrackingEndDate"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["TrackingEndDate"].ToString()),
                    RecorderUserId = dr["RecorderUserId"] == DBNull.Value ? 0 : Int32.Parse(dr["RecorderUserId"].ToString()),
                    RecordTime = dr["RecordTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["RecordTime"].ToString()),
                    RecordUserName = dr["RecordUserName"] == DBNull.Value ? "" : dr["RecordUserName"].ToString(),

                });
            }
            return returnlist;
        }

        public int GetObjListByConditionCount(int cuEmpId, string partName, string failureName,
          DateTime? sTrakingTime, DateTime? eTrakingTime)
        {
            StringBuilder sql = new StringBuilder(@"select count(tr.Id) allCount
              from Tbl_FailureTracking tr
              left join tbl_Part pt on pt.PartNo=tr.PartNo 
              left join Tbl_FailureInfo fi on fi.Id=tr.FailureId 
              where 1=1 and tr.Status=1");

            if (cuEmpId > 0)
                sql = sql.AppendFormat(@" and tr.ToEmpIds like '%,{0},%' ", cuEmpId);
            if (!string.IsNullOrEmpty(partName))
                sql = sql.AppendFormat(@" and pt.PartName like '%{0}%'", partName);
            if (!string.IsNullOrEmpty(failureName))
                sql = sql.AppendFormat(@" and fi.FailureName like '%{0}%'", failureName);

            if (sTrakingTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - tr.TrackingDate))>=0", sTrakingTime.Value.ToString("yyyy-MM-dd"));
            if (eTrakingTime.HasValue)
                sql = sql.AppendFormat(@" and ROUND(TO_NUMBER(to_date('{0}','yyyy-mm-dd') - tr.TrackingDate))<=0", eTrakingTime.Value.ToString("yyyy-MM-dd"));

            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool OverTracking(int objId)
        {
            string sql = "";
            sql = string.Format(@"update Tbl_FailureTracking set Status=0 where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

        public bool UpdateTracking(Tbl_FailureTracking cuObj)
        {
            string sql = string.Format(@"update Tbl_FailureTracking set TeamId={0},ToEmpIds='{1}',TrackingEndDate={2} where Id={3}",
              cuObj.TeamId, cuObj.ToEmpIds, (cuObj.TrackingEndDate.HasValue && cuObj.TrackingEndDate.Value.Year != 1900 ?
             "to_date('" + cuObj.TrackingEndDate.Value.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')" : "NULL"), cuObj.Id);

            return accessHelper.ExcultData(sql);
        }
        public bool DeleteTracking(Int32 objId)
        {
            string sql = string.Format(@"call DeleteTracking({0})", objId);
            return accessHelper.ExcultProc(sql);
        }
        #endregion

        #region trakingDescr
        public List<Tbl_FailureTrackingDescr> SaveTrakingDescr(Tbl_FailureTrackingDescr cuObj)
        {
            string sql = "";

            sql = string.Format(@"INSERT INTO Tbl_FailureTrackingDescr 
               (TrakingId,DESUSERID,DESCONTENT,DESTIME,DESCONTENTTYPE) VALUES({0},{1},'{2}',{3},{4})",
               cuObj.TrakingId, cuObj.DesUserId,
               cuObj.DesContent, (cuObj.DesTime.HasValue && cuObj.DesTime.Value.Year != 1900 ?
               "to_date('" + cuObj.DesTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')" : "NULL"),
               cuObj.DesContentType);

            var cuo = accessHelper.ExcultData(sql);
            if (!cuo)
                return null;
            List<Tbl_FailureTrackingDescr> returnlist = new List<Tbl_FailureTrackingDescr>();
            sql = string.Format("select * from Tbl_FailureTrackingDescr where TrakingId={0} and DESCONTENTTYPE={1}", cuObj.TrakingId, cuObj.DesContentType);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var cuTrk = new Tbl_FailureTrackingDescr()
                    {
                        Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                        DesContent = dr["DesContent"] == DBNull.Value ? "" : dr["DesContent"].ToString(),
                        DesContentType = dr["DesContentType"] == DBNull.Value ? 0 : Int32.Parse(dr["DesContentType"].ToString()),
                        DesTime = dr["DesTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["DesTime"].ToString()),
                        DesUserId = dr["DesUserId"] == DBNull.Value ? 0 : Int32.Parse(dr["DesUserId"].ToString()),
                        TrakingId = dr["TrakingId"] == DBNull.Value ? 0 : Int32.Parse(dr["TrakingId"].ToString())
                    };
                    returnlist.Add(cuTrk);
                }
            }
            return returnlist;
        }

        public List<Tbl_FailureTrackingDescr> GetTrakingDescrByTrakingId(int objId)
        {
            string sql = string.Format("select * from Tbl_FailureTrackingDescr where TrakingId={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_FailureTrackingDescr> returnlist = new List<Tbl_FailureTrackingDescr>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var cuTrk = new Tbl_FailureTrackingDescr()
                    {
                        Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                        DesContent = dr["DesContent"] == DBNull.Value ? "" : dr["DesContent"].ToString(),
                        DesContentType = dr["DesContentType"] == DBNull.Value ? 0 : Int32.Parse(dr["DesContentType"].ToString()),
                        DesTime = dr["DesTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["DesTime"].ToString()),
                        DesUserId = dr["DesUserId"] == DBNull.Value ? 0 : Int32.Parse(dr["DesUserId"].ToString()),
                        TrakingId = dr["TrakingId"] == DBNull.Value ? 0 : Int32.Parse(dr["TrakingId"].ToString())
                    };
                    returnlist.Add(cuTrk);
                }
            }
            return returnlist;
        }
        #endregion

        #region trakingAttachment
        public bool SaveTrakingAttachment(Tbl_TrakingAttachment cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
            {
                sql = string.Format(@"INSERT INTO Tbl_TrakingAttachment
        (TrakingId,en_FileName,FileName,FilePath,IsDefault
        ,ExtName,FileType,Sequence,UpDateTime,UpUserId,FileSize) VALUES({0},'{1}','{2}',
        '{3}',{4},'{5}','{6}',{7},{8},{9},'{10}')",
                   cuObj.TrakingId, cuObj.en_FileName,
                   cuObj.FileName, cuObj.FilePath, cuObj.IsDefault ? 1 : 0, cuObj.ExtName,
                   cuObj.FileType, cuObj.Sequence,
                   (cuObj.UpDateTime.HasValue && cuObj.UpDateTime.Value.Year != 1900 ?
                       "to_date('" + cuObj.UpDateTime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "','yyyy-MM-dd hh24:mi:ss')" : "NULL")
                  , cuObj.UpUserId, cuObj.FileSize);
                return accessHelper.ExcultData(sql);
            }

            return false;
        }

        public List<Tbl_TrakingAttachment> GetTrakingAttachmentByCondition(int trackingId,
          int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by Id desc) recordId,r.*       
      from Tbl_TrakingAttachment r 
      where 1=1");

            if (trackingId > 0)
                sql = sql.AppendFormat(@" and TrakingId={0} ", trackingId);

            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, @"select re.* from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);

            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            List<Tbl_TrakingAttachment> returnlist = new List<Tbl_TrakingAttachment>();
            foreach (DataRow dr in dt.Rows)
            {
                var cuo = new Tbl_TrakingAttachment
                {
                    Id = dr["Id"] == DBNull.Value ? 0 : Int32.Parse(dr["Id"].ToString()),
                    en_FileName = dr["en_FileName"] == DBNull.Value ? "" : dr["en_FileName"].ToString(),
                    FileName = dr["FileName"] == DBNull.Value ? "" : dr["FileName"].ToString(),
                    ExtName = dr["ExtName"] == DBNull.Value ? "" : dr["ExtName"].ToString(),
                    FilePath = dr["FilePath"] == DBNull.Value ? "" : dr["FilePath"].ToString(),
                    FileType = dr["FileType"] == DBNull.Value ? "" : dr["FileType"].ToString(),
                    IsDefault = dr["IsDefault"] == DBNull.Value ? false : dr["IsDefault"].ToString() == "1" ? true : false,
                    Sequence = dr["Sequence"] == DBNull.Value ? 0 : Int32.Parse(dr["Sequence"].ToString()),
                    TrakingId = dr["TrakingId"] == DBNull.Value ? 0 : Int32.Parse(dr["TrakingId"].ToString()),
                    UpDateTime = dr["UpDateTime"] == DBNull.Value ? (DateTime?)null : DateTime.Parse(dr["UpDateTime"].ToString()),
                    UpUserId = dr["UpUserId"] == DBNull.Value ? 0 : Int32.Parse(dr["UpUserId"].ToString()),
                    FileSize = dr["FileSize"] == DBNull.Value ? "" : dr["FileSize"].ToString()
                };
                cuo.FileName = cuo.FileName + cuo.ExtName;
                returnlist.Add(cuo);
            }
            return returnlist;
        }

        public int GetTrakingAttachmentCountByCondition(int trackingId)
        {
            StringBuilder sql = new StringBuilder(@"select count(tr.Id) allCount
      from Tbl_TrakingAttachment where 1=1");

            if (trackingId > 0)
                sql = sql.AppendFormat(@" and TrakingId={0} ", trackingId);

            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool DeleteTrakingAttachment(Int32 objId)
        {
            if (objId == 0)
                return false;
            string sql = string.Format(@"Delete from Tbl_TrakingAttachment where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

        public bool SetDefaultImg(Tbl_TrakingAttachment cuObj)
        {
            if (cuObj == null)
                return false;
            string sql = string.Format(@"call UpdateDefaultImg({0},{1})", cuObj.Id, cuObj.TrakingId);
            return accessHelper.ExcultProc(sql);
        }
        #endregion
    }
}
